﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace DAL
{
    public class NguoiMuaDAL
    {
        private SqlConnection cn = null;
        public NguoiMuaDAL()
        {
            string stringConnection = ConfigurationSettings.AppSettings["connectionstring"].ToString();
            cn = new SqlConnection(stringConnection);
            cn.Open();
        }

        public void INSERT(NguoiMua D)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_NGUOIMUA_Insert";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;

            cm.Parameters.AddWithValue("@TenDangNhap_NguoiMua", D.TenDangNhap_NguoiMua);
            cm.Parameters.AddWithValue("@MatKhau_NguoiMua", D.MatKhau_NguoiMua);
            cm.Parameters.AddWithValue("@TenNguoiMua", D.TenNguoiMua);
            cm.Parameters.AddWithValue("@CMND", D.CMND);
            cm.Parameters.AddWithValue("@DiaChi", D.DiaChi);
            cm.Parameters.AddWithValue("@DienThoai", D.DienThoai);
            cm.Parameters.AddWithValue("@DiaChi", D.DiaChi);
            cm.Parameters.AddWithValue("@DienThoai", D.DienThoai);

            cm.ExecuteNonQuery();
        }

        public void UPDATE(NguoiMua D)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_NGUOIMUA_Update";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;

            cm.Parameters.AddWithValue("@TenDangNhap_NguoiMua", D.TenDangNhap_NguoiMua);
            cm.Parameters.AddWithValue("@MatKhau_NguoiMua", D.MatKhau_NguoiMua);
            cm.Parameters.AddWithValue("@TenNguoiMua", D.TenNguoiMua);
            cm.Parameters.AddWithValue("@CMND", D.CMND);
            cm.Parameters.AddWithValue("@DiaChi", D.DiaChi);
            cm.Parameters.AddWithValue("@DienThoai", D.DienThoai);
            cm.Parameters.AddWithValue("@DiaChi", D.DiaChi);
            cm.Parameters.AddWithValue("@DienThoai", D.DienThoai);

            cm.ExecuteNonQuery();
        }
        public void UpdateTrangThai(string TenDangNhap_NguoiMua, int TrangThai)
        {
            SqlCommand command = new SqlCommand("sp_NguoiMua_UpdateTrangThai", cn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@TenDangNhap_NguoiMua", TenDangNhap_NguoiMua);
            command.Parameters.AddWithValue("@TrangThai", TrangThai);
            command.ExecuteNonQuery();

        }

        public void DELETE(string iD)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_NGUOIMUA_Delete";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@TenDangNhap_NguoiMua", iD);
            cm.ExecuteNonQuery();
        }

        public NguoiMuaCollection GET(string Where, int PageIndex, int PageSize, string OrderBy, string OrderDirection, out int TotalRecords)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_NGUOIMUA_Get";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@Where", Where);
            cm.Parameters.AddWithValue("@PageIndex", PageIndex);
            cm.Parameters.AddWithValue("@PageSize", PageSize);
            cm.Parameters.AddWithValue("@OrderBy", OrderBy);
            cm.Parameters.AddWithValue("@OrderDirection", OrderDirection);
            SqlParameter param = new SqlParameter("@TotalRecords", DbType.Int32);
            param.Direction = ParameterDirection.Output;
            cm.Parameters.Add(param);
            SqlDataAdapter da = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            NguoiMuaCollection NguoiMuaCollection = new NguoiMuaCollection();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                NguoiMua D = new NguoiMua();
                D.TenDangNhap_NguoiMua = ds.Tables[0].Rows[i]["TenDangNhap_NguoiMua"].ToString();
                D.MatKhau_NguoiMua = ds.Tables[0].Rows[i]["MatKhau_NguoiMua"].ToString();
                D.TenNguoiMua = ds.Tables[0].Rows[i]["TenNguoiMua"].ToString();
                D.CMND = ds.Tables[0].Rows[i]["CMND"].ToString();
                D.DiaChi = ds.Tables[0].Rows[i]["DiaChi"].ToString();
                D.DienThoai = ds.Tables[0].Rows[i]["DienThoai"].ToString();
                D.Email = ds.Tables[0].Rows[i]["Email"].ToString();
                D.TrangThai = int.Parse(ds.Tables[0].Rows[i]["TrangThai"].ToString());

                NguoiMuaCollection.Add(D);
            }
            TotalRecords = int.Parse(cm.Parameters["@TotalRecords"].Value.ToString());
            return NguoiMuaCollection;
        }
    }
}
